﻿Imports DocumentFormat.OpenXml.Spreadsheet
Imports FineUI
Imports Kimmo
Imports Microsoft.VisualBasic.CompilerServices
Imports System.Collections
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Diagnostics
Imports System.Runtime.CompilerServices

Public Class clsProjects
    Property ID As Integer = 0
    Property ProjectName As String = ""
    Property ProjectPath As String = ""
    Property LastUpdated As Nullable(Of DateTime)
    Property UpdatedBy As String = ""
    Property IsNew As Boolean = False
    Property FieldsToUpdate As String = "ID,ProjectName,ProjectPath,LastUpdated,UpdatedBy"
    Property DateFields As String = "LastUpdated"

    Public Sub New()
        MyBase.New()
        Me.ID = -1
        Me.ProjectName = ""
        Me.ProjectPath = ""
        Me.LastUpdated = Nothing
        Me.UpdatedBy = ""
    End Sub

    Public Shared Function ProjectNameByID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Projects", "ID", ID.ToString(), "ProjectName", ";", "Long")
        Return mv
    End Function

    Public Shared Function ProjectPathByID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Projects", "ID", ID.ToString(), "ProjectPath", ";", "Long")
        Return mv
    End Function

    Public Shared Sub DeleteByID(ByVal StrID As String)
        SQLCompact.RunQuery("delete from [Projects] where ID = " & Val(StrID), True)
    End Sub

    Public Sub Read(ByVal sID As String)
        Try
            Dim xset As New DataSet()
            xset = SQLCompact.RecordSeek("Projects", "ID", sID, "*", "Long")
            Dim xcount As Boolean = xset.Tables(0).Rows.Count - 1 <> -1
            If xcount Then
                Dim row As DataRow = xset.Tables(0).Rows(0)
                Me.ID = Val(row("ID").ToString())
                Me.ProjectName = row("ProjectName").ToString()
                Me.ProjectPath = row("ProjectPath").ToString()
                xcount = Microsoft.VisualBasic.CompilerServices.Operators.CompareString(row("LastUpdated").ToString(), "", False) = 0
                If Not xcount Then
                    Me.LastUpdated = CDate(row("LastUpdated"))
                Else
                    Me.LastUpdated = Nothing
                End If
                Me.UpdatedBy = row("UpdatedBy").ToString()
                Me.IsNew = False
                xset.Dispose()
            Else
                ID = -1
                Me.ProjectName = ""
                Me.ProjectPath = ""
                Me.UpdatedBy = ""
                Me.LastUpdated = Nothing
                Me.IsNew = True
            End If
        Catch exception1 As Exception
            Throw New Exception(exception1.Message)
        End Try
    End Sub

    Public Sub Save()
        Dim strCommand As String = ""
        Try
            Dim tableName As String = "Projects"
            Dim fldName As String = "ID"
            If Me.ID = -1 Then
                Me.ID = Val(SQLCompact.TableMaxValue(tableName, fldName, "")) + 1
                Me.IsNew = True
            Else
                Me.IsNew = False
            End If
            If Me.FieldsToUpdate = "*" Then Me.FieldsToUpdate = SQLCompact.Table_FieldNames(tableName, False, ",")
            Me.FieldsToUpdate = clsDL.MvRemoveDuplicates(Me.FieldsToUpdate, ",")
            Dim str2 As String = clsDL.MvPrefixBy(Me.FieldsToUpdate, "@", ",")
            If Me.IsNew = True Then
                strCommand = clsDL.BuildInsertCommand(tableName, Me.FieldsToUpdate, ",")
            Else
                strCommand = clsDL.BuildUpdateCommand(tableName, Me.FieldsToUpdate, fldName, ",")
            End If
            Dim spFlds() As String = {}
            Dim command As SqlCeCommand = SQLCompact.OpenCommand(strCommand)
            Dim totFlds As Integer = clsDL.StrParse(spFlds, Me.FieldsToUpdate, ",")
            command.Parameters.Clear()
            For fldCnt As Integer = 1 To totFlds
                Dim strSearch As String = spFlds(fldCnt)
                Dim d As Boolean = clsDL.MvSearch(Me.DateFields, strSearch, ",") > 0
                If Not d Then
                    command.Parameters.AddWithValue(String.Concat("@", strSearch), RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch)))))
                Else
                    Dim flag As Boolean = Strings.Len(strSearch) = 0
                    If Not flag Then
                        command.Parameters.AddWithValue(String.Concat("@", strSearch), RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(Interaction.IIf(Information.IsDate(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch))))), Conversions.ToDate(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch))), DBNull.Value)))))
                    Else
                        command.Parameters.AddWithValue(String.Concat("@", strSearch), DBNull.Value)
                    End If
                End If
            Next
            command.ExecuteNonQuery()
            SQLCompact.Connection.Close()
            command.Dispose()
        Catch exception1 As Exception
            Throw New Exception(exception1.Message)
        End Try
    End Sub

    Public Shared Sub ShowProjects(ByRef grdViews As FineUI.Grid, ByVal strFilter As String)
        clsFineUI.Grid.Clear(grdViews, "", "")
        clsFineUI.Grid.TableName = "Projects"
        clsFineUI.Grid.DataKeyNames = "ID"
        clsFineUI.Grid.PageSize = 50
        clsFineUI.Grid.AllowPaging = True
        clsFineUI.Grid.AllowSorting = True
        clsFineUI.Grid.LoadMask = False
        clsFineUI.Grid.Title = "Projects"
        clsFineUI.Grid.ShowBorder = False
        clsFineUI.Grid.ShowHeader = False
        clsFineUI.Grid.EnableRowNumber = False
        clsFineUI.Grid.AutoHeight = True
        clsFineUI.Grid.AutoPostBack = False
        clsFineUI.Grid.EnableCheckBoxSelect = True
        clsFineUI.Grid.AutoWidth = True
        clsFineUI.Grid.EnableAlternateRowColor = True
        clsFineUI.Grid.EnableCollapse = True
        clsFineUI.Grid.EnableMouseOverColor = True
        clsFineUI.Grid.EnableMultiSelect = True
        clsFineUI.Grid.EnableViewState = True
        clsFineUI.Grid.EnableAjax = True
        clsFineUI.Grid.ColumnLines = True
        clsFineUI.Grid.ToolTipField = "ProjectName"
        clsFineUI.Grid.IsDatabasePaging = False
        clsFineUI.Grid.ShowGridHeader = True
        clsFineUI.Grid.EnableHeaderMenu = True
        clsFineUI.Grid.EnableColumnHide = True
        clsFineUI.Grid.AddColumn("ID", "ID", "{0}", "", "", False, "30", TextAlign.Left, False)
        clsFineUI.Grid.AddTextBox("ProjectName", "Project Name", "{0}", "", "310", TextAlign.Left, False)
        clsFineUI.Grid.AddColumn("ProjectPath", "Project Path", "{0}", "", "", False, "310", TextAlign.Left, False)
        'clsFineUI.Grid.AddFileUpload("uploader", "Browse", "", "", "150", TextAlign.Left, False)
        clsFineUI.Grid.AddColumn("LastUpdated", "Date Updated", "{0:dd/MM/yyyy}", "", "", False, "110", TextAlign.Left, False)
        clsFineUI.Grid.AddColumn("UpdatedBy", "Updated By", "{0}", "", "", False, "110", TextAlign.Left, False)
        clsFineUI.Grid.AddSave()
        clsFineUI.Grid.AddClone()
        clsFineUI.Grid.AddTable()
        clsFineUI.Grid.AddDelete()
        If Len(strFilter) > 0 Then
            clsFineUI.Grid.Filter = strFilter
        End If
        clsFineUI.Grid.OrderBy = "ID"
        clsFineUI.Grid.BuildSelectCommand()
        clsFineUI.Grid.UseSQLCompact = True
        clsFineUI.Grid.Update(Nothing)
    End Sub

    Public Shared Sub TreeLoad(ByRef trvTree As FineUI.Tree)
        trvTree.Nodes.Clear()
        trvTree.AutoLeafIdentification = False
        Dim xTable As DataTable = SQLCompact.OpenDataTable("select ID,ProjectName from Projects order by ProjectName")
        For Each xRow As DataRow In xTable.Rows
            Dim strID As String = xRow("ID").ToString
            Dim strProjectName As String = xRow("ProjectName").ToString
            Dim pNode As FineUI.TreeNode = clsFineUI.TreeView.AddNode(trvTree, "project-" & strID, strProjectName, "powerpoint16", "project", strID)
            pNode.NavigateUrl = "frmTables.aspx?view=tables&projectid=" & strID
            pNode.Target = "rgnTables"
        Next
    End Sub
End Class
